﻿using System;
using System.Collections.Generic;
using System.Transactions;
using System.Data;
using System.Data.Common;

//using MySql.Data.MySqlClient;
//using MySql.Data.Types;

namespace Toenda.Foundation.Data {
	/// <summary>Provides functionality for basic database operations.</summary>
	/// <remarks>Connections to different databases are currently not supported in transaction contexts.</remarks>
	public class DAL : IDisposable {
		private static Dictionary<Transaction, DbConnection> _transConnections =
			new Dictionary<Transaction, DbConnection>();

		private DbConnection _translessConnection;

		private bool disposed;
		private DALSettings _settings;
		private string _connectionString;
		private DbProviderFactory _providerFactory;

		private int _commandTimeout;

		private string _configurationName;

		private static int _connectionCounter;

		#region Constructors / Destructors / Resource Management

		/// <summary>
		/// Initializes a new instance of the <see cref="DAL"/> class.
		/// </summary>
		/// <param name="dbConfig">The db config.</param>
		public DAL(DALSettings dbConfig) {
			this._settings = dbConfig;
			this._connectionString = dbConfig.ConnectionString;
			this._commandTimeout = -1;
		}

		/// <summary>
		/// Releases unmanaged resources and performs other cleanup operations before the
		/// <see cref="DAL"/> is reclaimed by garbage collection.
		/// </summary>
		~DAL() {
			this.Dispose(false);
		}

		/// <summary>
		/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
		/// </summary>
		public void Dispose() {
			this.Dispose(true);
			GC.SuppressFinalize(this);
		}

		/// <summary>
		/// Releases unmanaged and - optionally - managed resources
		/// </summary>
		/// <param name="disposing"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
		protected virtual void Dispose(bool disposing) {
			if(disposing) {
				if(this._translessConnection != null) {
					this._translessConnection.Dispose();
					this._translessConnection = null;
				}
			}
			this.disposed = true;
		}

		/// <summary>Creates a new DAL based on the specified connection string from the configuration file.</summary>
		/// <param name="connectionStringName">Name of the connection string defined in the configuration file.</param>
		/// <returns></returns>
		public static DAL FromConfiguration(string connectionStringName) {
			DAL dal = new DAL(DALSettings.FromConfiguration(connectionStringName));
			dal._configurationName = connectionStringName;
			return dal;
		}

		/// <summary>Creates a new DAL based on the specified connection string.</summary>
		/// <param name="connectionString">The connection string.</param>
		/// <returns></returns>
		public static DAL FromConnectionString(string connectionString) {
			DAL dal = new DAL(UdlParser.ParseConnectionString(connectionString));
			//dal._configurationName = connectionStringName;
			return dal;
		}

		#endregion

		#region Settings

		/// <summary>
		/// Gets the DAL settings
		/// </summary>
		public DALSettings Settings {
			get { return this._settings; }
		}

		/// <summary>
		/// Gets the name of the configuration
		/// </summary>
		public string ConfigurationName {
			get { return this._configurationName; }
		}

		/// <summary>
		/// Gets or sets the command timeout in milliseconds.
		/// </summary>
		/// <value>The command timeout.</value>
		public int CommandTimeout {
			get { return this._commandTimeout; }
			set { this._commandTimeout = value; }
		}

		/// <summary>
		/// Gets or sets the command timeout in seconds.
		/// </summary>
		/// <value>The command timeout in seconds.</value>
		public int CommandTimeoutInSeconds {
			get { return ( this._commandTimeout / 1000 ); }
			set {
				try {
					checked { this._commandTimeout = (value * 1000); }
				}
				catch(OverflowException) {
					this._commandTimeout = value;
				}
			}
		}

		#endregion

		#region Connection Handling

		/// <summary>
		/// Close the connection
		/// </summary>
		public void CloseConnection() {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			/* Connections bound to a transaction are automatically closed when the transaction completes. An explicit
			 * call to CloseConnection is ignored in this situation. */
			if(Transaction.Current == null) {
				if(this._translessConnection != null
				&& this._translessConnection.State == ConnectionState.Open) {
					this._translessConnection.Close();
					this._translessConnection = null;

					_connectionCounter--;
				}
			}
		}

		/// <summary>
		/// Returns True if the DAL is already connected to a database in the current transaction context,
		/// otherwise false.
		/// </summary>
		/// <value>
		/// 	<c>true</c> if this instance is connected; otherwise, <c>false</c>.
		/// </value>
		public bool IsConnected {
			get {
				Transaction curTrans = Transaction.Current;
				if(curTrans == null) {
					return this._translessConnection != null
					&& this._translessConnection.State == ConnectionState.Open;
				}
				else {
					/* Transactional connections are automatically removed from the connection dictionary 
					 * when they get closed, so all contained connections are open. */
					return _transConnections.ContainsKey(curTrans);
				}
			}
		}

		/// <summary>
		/// Open a connection
		/// </summary>
		/// <returns>
		/// True, if a new connection was opened or false if an existing connection is reused.
		/// </returns>
		public bool OpenConnection() {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			//Console.WriteLine("Open connection: {0}", _connectionCounter);
			//Console.WriteLine("Open connections: {0}", _transConnections.Count);

			//foreach(KeyValuePair<Transaction, DbConnection> pair in _transConnections) {
			//    Console.WriteLine("{0} :: {1}", pair.Key, pair.Value);
			//}

			Transaction curTrans = Transaction.Current;
			if(curTrans == null) {
				if(_translessConnection == null
				|| _translessConnection.State != ConnectionState.Open) {
					_translessConnection = CreateConnection();
					//Console.WriteLine(_translessConnection.ConnectionString);

					return true;
				}
				return false;
			}
			else {
				DbConnection con = null;
				lock(_transConnections) {
					if(_transConnections.TryGetValue(curTrans, out con)) {
						return false;
					}
					else {
						con = CreateConnection();
						con.StateChange += new StateChangeEventHandler(con_StateChange);
						curTrans.TransactionCompleted += new TransactionCompletedEventHandler(trans_TransactionCompleted);

						_transConnections.Add(curTrans, con);

						/* Transactional connections are automatically closed, when the transactions completes. A call
						 * to CloseConnection would be ignored. So false is returned even if a new connection was
						 * created, to prevent calling code to unnecessarily invoke CloseConnection. */
						return false;
					}
				}
			}
		}

		/// <summary>
		/// Handles the StateChange event of the con control.
		/// </summary>
		/// <param name="sender">The source of the event.</param>
		/// <param name="e">The <see cref="System.Data.StateChangeEventArgs"/> instance containing the event data.</param>
		private void con_StateChange(object sender, StateChangeEventArgs e) {
			if(e.CurrentState != ConnectionState.Open) {
				lock(_transConnections) {
					foreach(KeyValuePair<Transaction, DbConnection> pair in _transConnections) {
						if(pair.Value == sender) {
							_transConnections.Remove(pair.Key);
							break;
						}
					}
				}
			}
		}

		/// <summary>
		/// Handles the TransactionCompleted event of the trans control.
		/// </summary>
		/// <param name="sender">The source of the event.</param>
		/// <param name="e">The <see cref="System.Transactions.TransactionEventArgs"/> instance containing the event data.</param>
		private void trans_TransactionCompleted(object sender, TransactionEventArgs e) {
			lock(_transConnections) {
				/* Removing the connection from the transaction dictionary by transaction is faster than removing it
				 * in the connections StateChange event handler. So the event handler is removed before the connection
				 * gets closed. */
				DbConnection con = _transConnections[e.Transaction];
				con.StateChange -= new StateChangeEventHandler(con_StateChange);
				con.Close();

				_transConnections.Remove(e.Transaction);
			}
		}

		#endregion

		#region Command Execution

		/// <summary>
		/// Creates a command object for the current provider type.
		/// </summary>
		/// <returns>
		/// A new instance of a class derived from System.Data.Common.DbCommand.
		/// </returns>
		public DbCommand CreateCommand() {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			return ProviderFactory.CreateCommand();
		}

		/// <summary>
		/// Executes a command.
		/// </summary>
		/// <param name="cmd">The command to execute.</param>
		/// <returns>The number of records affected.</returns>
		public int ExecuteNonQuery(IDbCommand cmd) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(cmd == null) {
				throw new ArgumentNullException("cmd");
			}

			bool isNewConnection = this.OpenConnection();

			cmd.Connection = this.GetConnection();

			if(this._commandTimeout > 0) {
				cmd.CommandTimeout = this._commandTimeout;
			}
			else {
				cmd.CommandTimeout = 60;
			}

			int affectedRows = cmd.ExecuteNonQuery();
			if(this._settings.ProviderType != ProviderType.SQLite) {
				cmd.Connection = null;
			}

			if(isNewConnection) {
				this.CloseConnection();
			}

			return affectedRows;
		}

		/// <summary>
		/// Execute a non-query statement.
		/// </summary>
		/// <param name="query">The statement to execute.</param>
		/// <returns>The number of records affected.</returns>
		public int ExecuteNonQuery(string query) {
			IDbCommand cmd = this.CreateCommand();
			cmd.CommandText = query;

			return this.ExecuteNonQuery(cmd);
		}

		/// <summary>
		/// Execute a non-query statement, with an option to start it within a transaction.
		/// </summary>
		/// <param name="query">The statement to execute.</param>
		/// <param name="startAsTransaction">True if the statement shall be executed within a transaction, otherwise false.</param>
		/// <returns>The number of rows affected.</returns>
		/// <remarks>If startAsTransaction is set to true, the statement will be executed in a new transaction scope.
		/// This means that all changes made by the specified statement are persistent, even if a higher transaction
		/// scope fails. To execute more than one statement within a single transaction use the other ExecuteNonQuery
		/// methods enclosed in a transaction scope.</remarks>
		[Obsolete("Use TransactionScopes for transactional operations instead.")]
		public int ExecuteNonQuery(string query, bool startAsTransaction) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(query == null) {
				throw new ArgumentNullException("query");
			}

			int affectedRows;

			if(startAsTransaction) {
				using(TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew)) {
					affectedRows = this.ExecuteNonQuery(query);
					scope.Complete();
				}
			}
			else {
				affectedRows = this.ExecuteNonQuery(query);
			}

			return affectedRows;
		}

		/// <summary>
		/// Executes a query
		/// </summary>
		/// <param name="query">The query to execute.</param>
		/// <returns>
		/// A System.Data.DataSet containing the query results.
		/// </returns>
		public DataSet ExecuteQuery(string query) {
			return this.ExecuteQuery(query, CommandType.Text);
		}

		/// <summary>
		/// Executes a query
		/// </summary>
		/// <param name="query">The query to execute.</param>
		/// <param name="cmdType">Command type of the query string.</param>
		/// <returns>
		/// A System.Data.DataSet containing the query results.
		/// </returns>
		public DataSet ExecuteQuery(string query, CommandType cmdType) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(query == null) {
				throw new ArgumentNullException("query");
			}

			DataSet data = new DataSet();

			bool isNewConnection = this.OpenConnection();

			IDbCommand cmd = this.CreateCommand();

			cmd.Connection = this.GetConnection();
			cmd.CommandText = query;
			if(this._commandTimeout > 0) {
				cmd.CommandTimeout = this._commandTimeout;
			}
			else {
				cmd.CommandTimeout = 60;
			}
			cmd.CommandType = cmdType;

			IDbDataAdapter adp = this.GetDataAdapter(cmd);
			adp.Fill(data);

			if(isNewConnection) {
				this.CloseConnection();
			}

			return data;
		}

		/// <summary>
		/// Executes a command.
		/// </summary>
		/// <param name="cmd">The command to execute.</param>
		/// <returns>
		/// An IDataReader for the resultset of the executed command.
		/// </returns>
		public IDataReader ExecuteQueryForDataReader(IDbCommand cmd) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(cmd == null) {
				throw new ArgumentNullException("cmd");
			}

			DbConnection con = this.GetConnection();

			// TODO: check for correct db???
			//if(con.ConnectionString.Contains("=data") && this.ConfigurationName.Contains("indexen")) {
			//    con = GetConnection();
			//    Console.WriteLine("error");
			//}

			if(con == null) {
				throw new InvalidOperationException("Database connection not available.");
			}

			if(this._commandTimeout > 0) {
				cmd.CommandTimeout = this._commandTimeout;
			}
			else {
				cmd.CommandTimeout = 60;
			}
			cmd.Connection = con;

			IDataReader reader = cmd.ExecuteReader();

			if(this._settings.ProviderType != ProviderType.SQLite) {
				cmd.Connection = null;
			}

			return reader;
		}

		/// <summary>Executes a command.</summary>
		/// <param name="cmd">The command to execute.</param>
		/// <returns>A DbDataReader for the resultset of the executed command.</returns>
		public DbDataReader ExecuteQueryForDataReader(DbCommand cmd) {
			return (DbDataReader)this.ExecuteQueryForDataReader((IDbCommand)cmd);
		}

		/// <summary>Executes a query.</summary>
		/// <param name="query">The query to execute.</param>
		/// <param name="cmdType">Type of the query to executed.</param>
		/// <returns>A System.Data.IDataReader for the resultset of the executed query.</returns>
		public IDataReader ExecuteQueryForDataReader(string query, CommandType cmdType) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(query == null) {
				throw new ArgumentNullException("query");
			}

			IDbCommand cmd = this.CreateCommand();
			cmd.CommandText = query;
			cmd.CommandType = cmdType;

			return this.ExecuteQueryForDataReader(cmd);
		}

		/// <summary>
		/// Executes the query for data table reader.
		/// </summary>
		/// <param name="cmd">The CMD.</param>
		/// <returns></returns>
		public DataTableReader ExecuteQueryForDataTableReader(IDbCommand cmd) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(cmd == null) {
				throw new ArgumentNullException("cmd");
			}

			// TODO: Does this method really work? Can the connection be closed?
			bool isNewConnection = this.OpenConnection();

			if(this._commandTimeout > 0) {
				cmd.CommandTimeout = this._commandTimeout;
			}
			else {
				cmd.CommandTimeout = 60;
			}

			cmd.Connection = this.GetConnection();

			DataTableReader reader = null;

			IDbDataAdapter adp = this.GetDataAdapter(cmd);
			DataSet data = new DataSet();
			adp.Fill(data);

			if(data != null) {
				reader = data.CreateDataReader();
			}

			if(isNewConnection) {
				this.CloseConnection();
			}

			return reader;
		}

		/// <summary>Executes a query</summary>
		/// <param name="query">The query to execute.</param>
		/// <param name="cmdType">Type of the query to execute.</param>
		/// <returns>A System.Data.DataTableReader containing the results of the query.</returns>
		public DataTableReader ExecuteQueryForDataTableReader(string query, CommandType cmdType) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(query == null) {
				throw new ArgumentNullException("query");
			}

			// TODO: Does this method really work? Can the connection be closed?
			bool isNewConnection = this.OpenConnection();

			IDbCommand cmd = this._translessConnection.CreateCommand();
			cmd.CommandText = query;
			if(this._commandTimeout > 0) {
				cmd.CommandTimeout = this._commandTimeout;
			}
			else {
				cmd.CommandTimeout = 60;
			}
			cmd.CommandType = cmdType;
			cmd.Connection = this.GetConnection();

			DataTableReader reader = null;

			IDbDataAdapter adp = this.GetDataAdapter(cmd);
			DataSet data = new DataSet();
			adp.Fill(data);

			if(data != null) {
				reader = data.CreateDataReader();
			}

			if(isNewConnection) {
				this.CloseConnection();
			}

			return reader;
		}

		/// <summary>Executes a query for a single value.</summary>
		/// <param name="cmd">The command to execute.</param>
		/// <returns></returns>
		public object ExecuteScalar(IDbCommand cmd) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(cmd == null) {
				throw new ArgumentNullException("cmd");
			}

			object result;

			bool isNewConnection = this.OpenConnection();

			if(this._commandTimeout > 0) {
				cmd.CommandTimeout = this._commandTimeout;
			}
			else {
				cmd.CommandTimeout = 60;
			}
			cmd.Connection = this.GetConnection();
			result = cmd.ExecuteScalar();

			if(isNewConnection) {
				this.CloseConnection();
			}

			return result;
		}

		/// <summary>Executes a query for a single value.</summary>
		/// <param name="query">The query to execute.</param>
		/// <param name="cmdType">Type of the command.</param>
		/// <returns>The first cell in the first row of the resultset.</returns>
		public object ExecuteScalar(string query, CommandType cmdType) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(query == null) {
				throw new ArgumentNullException("query");
			}

			IDbCommand cmd = this.CreateCommand();
			cmd.CommandText = query;
			cmd.CommandType = cmdType;

			return this.ExecuteScalar(cmd);
		}

		/// <summary>Executes an update.</summary>
		/// <param name="data">The System.Data.DataSet containing the data to be transferred to the database.</param>
		/// <param name="table">Name of the table to update.</param>
		/// <returns>The number of rows updated.</returns>
		public int ExecuteUpdate(DataSet data, string table) {
			if(disposed) {
				throw new ObjectDisposedException(this.GetType().ToString());
			}

			if(data == null) {
				throw new ArgumentNullException("data");
			}

			if(table == null) {
				throw new ArgumentNullException("table");
			}

			int num = 0;

			bool isNewConnection = this.OpenConnection();

			DbCommand cmdSelect = this.ProviderFactory.CreateCommand();
			cmdSelect.CommandText = string.Format("SELECT * FROM {0}", table);
			cmdSelect.Connection = this.GetConnection();

			DbDataAdapter adp = this.ProviderFactory.CreateDataAdapter();
			adp.SelectCommand = cmdSelect;

			DbCommandBuilder cmdBuilder = this.ProviderFactory.CreateCommandBuilder();
			cmdBuilder.DataAdapter = adp;

			adp.UpdateCommand = cmdBuilder.GetUpdateCommand();

			num = adp.Update(data);

			if(isNewConnection) {
				this.CloseConnection();
			}

			return num;
		}
		 
		#endregion

		#region Private Helper-Methods

		/// <summary>Creates a new connection using the current DALSettings.</summary>
		/// <returns>A new database connection, connected to the configured database.</returns>
		private DbConnection CreateConnection() {
			DbConnection newCon = ProviderFactory.CreateConnection();

			if(this._connectionString != this._settings.ConnectionString) {
				this._connectionString = this._settings.ConnectionString;
			}

			newCon.ConnectionString = this._connectionString;

			//int connectionCounter = _connectionCounter;

			try {
				newCon.Open();

				_connectionCounter++;

				return newCon;
			}
			catch(Exception) {
				//while(_connectionCounter >= connectionCounter) {
				//    Console.WriteLine("waiting for new connection...");
				//    System.Threading.Thread.Sleep(100);
				//}

				newCon.Open();

				_connectionCounter++;

				return newCon;
			}
		}

		/// <summary>Requests a database connection suitable for the current context.</summary>
		/// <returns>A transactional or nontransactional database connection.</returns>
		private DbConnection GetConnection() {
			Transaction curTrans = Transaction.Current;
			if(curTrans == null) {
				//if(this._translessConnection.ConnectionString != this._settings.CompleteString) {
				//    _translessConnection = CreateConnection();
				//}

				return this._translessConnection;
			}
			else {
				DbConnection con = null;
				_transConnections.TryGetValue(curTrans, out con);
				return con;
			}
		}

		/// <summary>Get a data adapter from a command</summary>
		/// <param name="cmd"></param>
		/// <returns></returns>
		private IDbDataAdapter GetDataAdapter(IDbCommand cmd) {
			IDbDataAdapter adp = ProviderFactory.CreateDataAdapter();

			adp.SelectCommand = cmd;

			return adp;
		}

		/// <summary>Returns a factory for the current provider.</summary>
		private DbProviderFactory ProviderFactory {
			get {
				if(this._providerFactory == null) {
					//if(this._settings.ProviderType == ProviderType.SQLite) {
					//    this._providerFactory = SQLiteFactory.Instance;
					//}
					//else {
						this._providerFactory = DbProviderFactories.GetFactory(this._settings.ProviderName);
					//}
				}
				return this._providerFactory;
			}
		}

		#endregion
	}
}
